package com.roc.web.action.table;

import java.util.ArrayList;
import java.util.List;

import com.roc.page.BaseSearchCondition;
import com.roc.page.WhereHQLFieldBean;
import com.roc.util.Constance;
import com.roc.util.constant.Role;

public class MerinfoTermSearchCondtion  extends BaseSearchCondition{
	private static final long serialVersionUID = 1L;

	
	private String 	crtMinDate; // 创建开始时间(年月日)
	private String 	crtMaxDate; // 创建结束时间(年月日)
	
	private String orgCode;//收单机构号
	private String loginId; //登录人id
	private String roleId; //角色 
	private long agentId;//所属代理商
	private String merchantNo;//商户号
	private String merchantName;//商户简称
	private String  merBrName; //注册名称
	private String status;//状态
	private int  merinfoFlag;//商户状态
	private String obj;//所有下级代理
	private String opId;//操作员id
	private String  certifName;//法人
	private String  cardNo; //法人身份证
	private String  contactName;//业务联系人
	private String  phone;//联系电话 
	private String areaCode;//地区
	private String  mccName;//mcc
	private String  rateType;//扣率类型
	private String  minDate;//入网时间
	private String  maxDate;//入网时间
	private String  midType;//商户类型
	private String  parentCode;//父类
	private String  sonCode;//子类
	private String  accountNo;//账号 
	private String  accountName;//账号 
	
	private String  cycle;//结算周期
	private String  addr;//商户地址
	private String realName;//所属代理商中文
	
	private String clearCardId; //结算人身份证
	private String directlyAgent; //直属代理商
	private String association;//关联关系
	private String busiLiceNo; //营业执照号码
	private String superRole; //上级角色
	private String oneOrgCode; //机构号
	private String groupCd; //受理市场标识
	
	
	@Override
	protected List<WhereHQLFieldBean> getWhereHQLFieldBeanList() {
List<WhereHQLFieldBean> filedBeanList = new ArrayList<WhereHQLFieldBean>();
        //商户号
		if(Constance.isNotEmpty(merchantNo)){ 
			filedBeanList.add(new WhereHQLFieldBean("t.MCHT_CD", merchantNo.trim(), "=", false));
		}
		//商户简称 （模糊）
		if(Constance.isNotEmpty(merchantName)){ 
			filedBeanList.add(new WhereHQLFieldBean("t.NAME_BUSI", merchantName.trim(), "like", false,"ALL"));
		}
		//商户名称 （模糊）
		if(Constance.isNotEmpty(merBrName)){ 
			filedBeanList.add(new WhereHQLFieldBean("t.NAME", merBrName.trim(), "like", false,"ALL"));
		}
		//入网时间
		if(Constance.isNotEmpty(minDate)&&Constance.isNotEmpty(maxDate)){ 
			filedBeanList.add(new WhereHQLFieldBean("t.APPL_DATE",minDate.trim(),">=", false));
       	    filedBeanList.add(new WhereHQLFieldBean("t.APPL_DATE",maxDate.trim(),"<=", false));
		}
		//法人	
		if(Constance.isNotEmpty(certifName)){ 
			filedBeanList.add(new WhereHQLFieldBean("t.CERTIF", certifName.trim(), "like", false,"ALL"));
		}
		//法人身份证号
		if(Constance.isNotEmpty(cardNo)){ 
			filedBeanList.add(new WhereHQLFieldBean("t.CERTIF_NO", cardNo.trim(), "=", false));
		}
		//业务联系人
		if(Constance.isNotEmpty(contactName)){ 
			filedBeanList.add(new WhereHQLFieldBean("t.CONTACT_NAME", contactName.trim() ,"like", false,"ALL"));
		} 
		//业务联系电话
		if(Constance.isNotEmpty(phone)){ 
			filedBeanList.add(new WhereHQLFieldBean("t.CONTACT_PHONENO", phone.trim(), "=", false));
		} 
		 //结算账号
		if(Constance.isNotEmpty(accountNo)){
			filedBeanList.add(new WhereHQLFieldBean("B.ACCOUNT", accountNo.trim(), "=", false));
		} 
		//结算账户名称
		if(Constance.isNotEmpty(accountName)){ 
			filedBeanList.add(new WhereHQLFieldBean("B.NAME", accountName.trim() ,"like", false,"ALL"));
		}
		//商户非法人身份证
		if(Constance.isNotEmpty(clearCardId)){
			filedBeanList.add(new WhereHQLFieldBean("t.STLM_WAY_DESC", clearCardId.trim(), "=", false));
		}
		//直属代理商--
		if(Constance.isNotEmpty(directlyAgent)){
			filedBeanList.add(new WhereHQLFieldBean("OP.real_name", directlyAgent.trim(), "=", false));
		}
		 //商户状态
		if(Constance.isNotEmpty(status) && !status.trim().equals("99")){
			filedBeanList.add(new WhereHQLFieldBean("t.STATUS", status.trim(), "=", false));
		}
		
		if(Constance.isNotEmpty(busiLiceNo)){	//营业执照号
			filedBeanList.add(new WhereHQLFieldBean("T.BUSI_LICE_NO", busiLiceNo.trim(), "=", false));
		}
		//创建时间
		if (Constance.isNotEmpty(crtMinDate) && Constance.isNotEmpty(crtMaxDate)) {
			//Date startDate = Constance.formateDate(minDate);
			//Date endDate = Constance.formateDate(maxDate);
			String startDate = crtMinDate.replace("-", "");
			String endDate = crtMaxDate.replace("-", "");
			filedBeanList.add(new WhereHQLFieldBean("to_char(t.rec_crt_ts,'yyyyMMdd')", startDate,
					">=", false));
			filedBeanList.add(new WhereHQLFieldBean("to_char(t.rec_crt_ts,'yyyyMMdd')", endDate,
					"<=", false));
		}
				
	/*	//机构
		if(Constance.isNotEmpty(oneOrgCode)&&!roleId.equals(Role.SYSTEM.getCode())&&!roleId.equals(Role.AGENT.getCode())&&!(roleId.equals(Role.MARKET_DIRECT.getCode())||roleId.equals(Role.MARKET_LEAD.getCode()))){//机构
			filedBeanList.add(new WhereHQLFieldBean("t.uc_bc_cd_area", oneOrgCode.trim(), "=", false));
		}
		//所属机构
		if(Constance.isNotEmpty(orgCode)){
			filedBeanList.add(new WhereHQLFieldBean("t.BANK_BELONG_CD", orgCode.trim(), "=", false));
		}*/
		return filedBeanList;
	
	}

	@Override
	protected String getRowCountHQL() {
		// TODO Auto-generated method stub
		StringBuffer  sb= new StringBuffer();
		sb.append("SELECT  count(1)  ");
		sb.append(" FROM  apsmgm.tbl_mcht_inf T    ");
		sb.append(" LEFT JOIN apsmgm.TBL_TERM_INF   TM  ON  t.mcht_cd=tm.mcht_cd  ");
		sb.append("  LEFT JOIN apsmgm.TBL_MCHT_BANKACCOUNT B  ON  t.mcht_cd=B.OWNER_CD  ");
		sb.append("  LEFT JOIN apsmgm.T_OPERATORS OP  ON  t.EXT1=TO_NUMBER(OP.ID)  ");
		sb.append(" WHERE  T.GROUP_CD='2'   AND T.BANK_BELONG_CD='48502000' ");
		return sb.toString();
	}

	@Override
	protected String getSearchHQL() {
		// TODO Auto-generated method stub
		StringBuffer  sb= new StringBuffer();
		sb.append(" SELECT   t.mcht_cd ,tm.term_id,tm.status  tmStatus,t.name,t.name_busi, ");
		sb.append(" CASE '1000' WHEN (SELECT DISTINCT PROD_CD FROM apsmgm.TBL_MCHT_BIZ_DEAL y WHERE y.MCHT_CD = t .MCHT_CD AND y.PROD_CD = '1000') THEN 'T1' ELSE '0' END  T1_type, ");
		sb.append(" CASE '1171' WHEN (SELECT DISTINCT PROD_CD FROM apsmgm.TBL_MCHT_BIZ_DEAL y WHERE y.MCHT_CD = t .MCHT_CD AND y.PROD_CD = '1171') THEN 'T0' ELSE '0' END  T0_type, ");
		sb.append(" t.BUSI_LICE_NO,t.reg_addr,tm.INSTALLLOCATION, t.certif,t.APPL_DATE, t.status,  ");
		sb.append(" ( SELECT O.REAL_NAME FROM APSMGM.T_OPERATORS  O  WHERE  O.ID=TO_NUMBER(T.EXT1) )zhiName , ");//直属代理商
		sb.append("(SELECT A.ORG_NAME FROM APSMGM.TBL_ORG_MAIN A WHERE A.org_code= OP.ONEAGENTID  ) OneOrgName, ");//一级代理商
		sb.append("(SELECT b.name FROM  apsmgm.tbl_area  b WHERE b.id=op.companyid )  comName , ");  //所属大区
		sb.append(" t.contact_name,t.contact_phoneno,t.mcc_cd_18, ");
		sb.append(" t.prov_cd,t.city_cd, OP.REAL_NAME ,  ");
		sb.append("  case tm.TERM_TP  when '1' then '固定'  when '3' then '移动' else '其他' end ," );
		sb.append(" tm.DEVICE_CD, " );
		sb.append(" (SELECT TP.REAL_NAME  FROM apsmgm.T_OPERATORS TP  WHERE TP.ID = TO_NUMBER(T.REC_UPD_OPR))  jinName,  ");
		sb.append(" t.busi_main, tm.REC_CRT_TS,  ");
		sb.append(" (SELECT  FE.MCHT_FEE_PERCENT   FROM apsmgm.TBL_MCHT_BIZ_FEE FE  WHERE  fe.sub_biz_cd='0001000' AND fe.prod_cd='1000'  AND fe.mcht_cd=t.mcht_cd)  T1_J, ");
		sb.append(" (SELECT  FE.MCHT_FEE_PCT_MAX   FROM apsmgm.TBL_MCHT_BIZ_FEE FE  WHERE  fe.sub_biz_cd='0001000' AND fe.prod_cd='1000'  AND fe.mcht_cd=t.mcht_cd)  T1_f, ");
		sb.append(" (SELECT  FE.MCHT_FEE_PERCENT   FROM apsmgm.TBL_MCHT_BIZ_FEE FE  WHERE  fe.sub_biz_cd='0001001' AND fe.prod_cd='1000'  AND fe.mcht_cd=t.mcht_cd)  T1_D, ");
		sb.append(" (SELECT  FE.MCHT_FEE_PERCENT   FROM apsmgm.TBL_MCHT_BIZ_FEE FE  WHERE  fe.sub_biz_cd='0002000' AND fe.prod_cd='1171'  AND fe.mcht_cd=t.mcht_cd)  T0_J, ");
		sb.append(" (SELECT  FE.MCHT_FEE_PERCENT   FROM apsmgm.TBL_MCHT_BIZ_FEE FE  WHERE  fe.sub_biz_cd='0002001' AND fe.prod_cd='1171'  AND fe.mcht_cd=t.mcht_cd)  T0_D, ");
		sb.append("   t.APPROVALUSERNAME ,b.name accName,b.account, b.bank_name, ");
		sb.append(" CASE '1021' WHEN (SELECT DISTINCT Y.TRANS_CD FROM apsmgm.TBL_MCHT_BIZ_DEAL y WHERE y.MCHT_CD = t .MCHT_CD AND y.TRANS_CD = '1021') THEN '是' ELSE '否' END  YSQ ,");

		
		sb.append(" (SELECT  FE.MCHT_FEE_PERCENT   FROM apsmgm.TBL_MCHT_BIZ_FEE FE  WHERE  fe.sub_biz_cd='0001004' and fe.prod_cd='1391'  AND fe.mcht_cd=t.mcht_cd) ZFB_S,   ");
		sb.append(" (SELECT  FE.MCHT_FEE_PERCENT   FROM apsmgm.TBL_MCHT_BIZ_FEE FE  WHERE  fe.sub_biz_cd='0001004' and fe.prod_cd='1191'  AND fe.mcht_cd=t.mcht_cd) YL_S ,  ");
		sb.append(" TM.REC_UPD_TS   ");
		sb.append(" ,(SELECT m.superparentname  FROM apsmgm.t_mcc_detail  m  WHERE m.code=t.mcc_cd_18) mccType ");
		sb.append(" FROM  apsmgm.tbl_mcht_inf T    ");
		sb.append(" LEFT JOIN apsmgm.TBL_TERM_INF   TM  ON  t.mcht_cd=tm.mcht_cd  ");
		sb.append("  LEFT JOIN apsmgm.TBL_MCHT_BANKACCOUNT B  ON  t.mcht_cd=B.OWNER_CD  ");
		sb.append("  LEFT JOIN apsmgm.T_OPERATORS OP  ON  t.EXT1=TO_NUMBER(OP.ID)  ");
		sb.append(" WHERE  T.GROUP_CD='2'  AND T.BANK_BELONG_CD='48502000' ");
		return sb.toString();
	}

	@Override
	protected String getOrderPartOfHQL() {
		// TODO Auto-generated method stub
		return " ORDER BY t.APPL_DATE DESC  ";
	}

	public String getOrgCode() {
		return orgCode;
	}

	public void setOrgCode(String orgCode) {
		this.orgCode = orgCode;
	}

	public String getLoginId() {
		return loginId;
	}

	public void setLoginId(String loginId) {
		this.loginId = loginId;
	}

	public String getRoleId() {
		return roleId;
	}

	public void setRoleId(String roleId) {
		this.roleId = roleId;
	}

	public long getAgentId() {
		return agentId;
	}

	public void setAgentId(long agentId) {
		this.agentId = agentId;
	}

	public String getMerchantNo() {
		return merchantNo;
	}

	public void setMerchantNo(String merchantNo) {
		this.merchantNo = merchantNo;
	}

	public String getMerchantName() {
		return merchantName;
	}

	public void setMerchantName(String merchantName) {
		this.merchantName = merchantName;
	}

	public String getMerBrName() {
		return merBrName;
	}

	public void setMerBrName(String merBrName) {
		this.merBrName = merBrName;
	}

	public String getStatus() {
		return status;
	}

	public void setStatus(String status) {
		this.status = status;
	}

	public int getMerinfoFlag() {
		return merinfoFlag;
	}

	public void setMerinfoFlag(int merinfoFlag) {
		this.merinfoFlag = merinfoFlag;
	}

	public String getObj() {
		return obj;
	}

	public void setObj(String obj) {
		this.obj = obj;
	}

	public String getOpId() {
		return opId;
	}

	public void setOpId(String opId) {
		this.opId = opId;
	}

	public String getCertifName() {
		return certifName;
	}

	public void setCertifName(String certifName) {
		this.certifName = certifName;
	}

	public String getCardNo() {
		return cardNo;
	}

	public void setCardNo(String cardNo) {
		this.cardNo = cardNo;
	}

	public String getContactName() {
		return contactName;
	}

	public void setContactName(String contactName) {
		this.contactName = contactName;
	}

	public String getPhone() {
		return phone;
	}

	public void setPhone(String phone) {
		this.phone = phone;
	}

	public String getAreaCode() {
		return areaCode;
	}

	public void setAreaCode(String areaCode) {
		this.areaCode = areaCode;
	}

	public String getMccName() {
		return mccName;
	}

	public void setMccName(String mccName) {
		this.mccName = mccName;
	}

	public String getRateType() {
		return rateType;
	}

	public void setRateType(String rateType) {
		this.rateType = rateType;
	}

	public String getMinDate() {
		return minDate;
	}

	public void setMinDate(String minDate) {
		this.minDate = minDate;
	}

	public String getMaxDate() {
		return maxDate;
	}

	public void setMaxDate(String maxDate) {
		this.maxDate = maxDate;
	}

	public String getMidType() {
		return midType;
	}

	public void setMidType(String midType) {
		this.midType = midType;
	}

	public String getParentCode() {
		return parentCode;
	}

	public void setParentCode(String parentCode) {
		this.parentCode = parentCode;
	}

	public String getSonCode() {
		return sonCode;
	}

	public void setSonCode(String sonCode) {
		this.sonCode = sonCode;
	}

	public String getAccountNo() {
		return accountNo;
	}

	public void setAccountNo(String accountNo) {
		this.accountNo = accountNo;
	}

	public String getAccountName() {
		return accountName;
	}

	public void setAccountName(String accountName) {
		this.accountName = accountName;
	}

	public String getCycle() {
		return cycle;
	}

	public void setCycle(String cycle) {
		this.cycle = cycle;
	}

	public String getAddr() {
		return addr;
	}

	public void setAddr(String addr) {
		this.addr = addr;
	}

	public String getRealName() {
		return realName;
	}

	public void setRealName(String realName) {
		this.realName = realName;
	}

	public String getClearCardId() {
		return clearCardId;
	}

	public void setClearCardId(String clearCardId) {
		this.clearCardId = clearCardId;
	}

	public String getDirectlyAgent() {
		return directlyAgent;
	}

	public void setDirectlyAgent(String directlyAgent) {
		this.directlyAgent = directlyAgent;
	}

	public String getAssociation() {
		return association;
	}

	public void setAssociation(String association) {
		this.association = association;
	}

	public String getBusiLiceNo() {
		return busiLiceNo;
	}

	public void setBusiLiceNo(String busiLiceNo) {
		this.busiLiceNo = busiLiceNo;
	}

	public String getSuperRole() {
		return superRole;
	}

	public void setSuperRole(String superRole) {
		this.superRole = superRole;
	}

	public String getOneOrgCode() {
		return oneOrgCode;
	}

	public void setOneOrgCode(String oneOrgCode) {
		this.oneOrgCode = oneOrgCode;
	}

	public String getGroupCd() {
		return groupCd;
	}

	public void setGroupCd(String groupCd) {
		this.groupCd = groupCd;
	}

	public String getCrtMinDate() {
		return crtMinDate;
	}

	public void setCrtMinDate(String crtMinDate) {
		this.crtMinDate = crtMinDate;
	}

	public String getCrtMaxDate() {
		return crtMaxDate;
	}

	public void setCrtMaxDate(String crtMaxDate) {
		this.crtMaxDate = crtMaxDate;
	}

	
	
	
}
